﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC12BO: Chi tiet dat tour
    /// </summary>
    public class VYC12BO : DBConnectionBO
    {
        public VYC12BO()
            : base()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        /// <summary>
        /// Lay danh sach cac du lieu trong VYC12
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC12> GetAllList()
        {
            var list = new List<ModelVYC12>();
            const string query = "SELECT [Id],[IdDatTour],[HoTen],[NgaySinh],[DiaChi],[GioiTinh],[IdLoaiKhach],[IdDoTuoi],[PhongDon],[GiaTien],[NgayCapNhat],[TrangThai] FROM [VYC-12]";
            var sqlCommand = new SqlCommand(query, connection);
            var reader = sqlCommand.ExecuteReader();
            CloseConnection();
            while (reader.Read())
            {
                var model = new ModelVYC12();

                model.Id = Convert.ToInt32(reader["Id"]);
                model.IdDatTour = reader["IdDatTour"].ToString();
                model.HoTen = reader["HoTen"].ToString();
                model.NgaySinh = (DateTime) reader["NgaySinh"];
                model.DiaChi = reader["DiaChi"].ToString();
                model.GioiTinh = Convert.ToInt32(reader["GioiTinh"]);
                model.IdLoaiKhach = Convert.ToInt32(reader["IdLoaiKhach"]);
                model.IdDoTuoi = Convert.ToInt32(reader["IdDoTuoi"]);
                model.PhongDon = reader["PhongDon"].ToString();
                model.GiaTien = Convert.ToDecimal(reader["GiaTien"]);
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);


                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC12 model)
        {
            var query = "INSERT INTO [VYC-12]([IdDatTour],[HoTen],[NgaySinh],[DiaChi],[GioiTinh],[IdLoaiKhach],[IdDoTuoi],[PhongDon],[GiaTien],[NgayCapNhat],[TrangThai]) " +
                        "VALUES (@iddattour,@hoten, @ngaysinh, @diachi, @gioitinh , @idloaikhach, @iddotuoi, @phongdon , @giatien ,"
                        + "GETDATE(), @trangthai )";

            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@iddattour", SqlDbType.VarChar).Value = model.IdDatTour;
            sqlCommand.Parameters.Add("@hoten", SqlDbType.NVarChar).Value = model.HoTen;
            sqlCommand.Parameters.Add("@ngaysinh", SqlDbType.DateTime).Value = model.NgaySinh;
            sqlCommand.Parameters.Add("@diachi", SqlDbType.NVarChar).Value = model.DiaChi;
            sqlCommand.Parameters.Add("@gioitinh", SqlDbType.Bit).Value = model.GioiTinh;
            sqlCommand.Parameters.Add("@idloaikhach", SqlDbType.Int).Value = model.IdLoaiKhach;
            sqlCommand.Parameters.Add("@iddotuoi", SqlDbType.Int).Value = model.IdDoTuoi;
            sqlCommand.Parameters.Add("@phongdon", SqlDbType.VarChar).Value = model.PhongDon;
            sqlCommand.Parameters.Add("@giatien", SqlDbType.Decimal).Value = model.GiaTien;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;

            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC12 model)
        {
            var query = "UPDATE [VYC-12] SET " +
                        "IdDatTour= @iddattour"+
                        ",HoTen= @hoten" +
                        ",NgaySinh= @ngaysinh" +
                        ",DiaChi= @diachi"+
                        ",GioiTinh= @gioitinh"+
                        ",IdLoaiKhach= @idloaikhach"+
                        ",IdDoTuoi= @iddotuoi"+
                        ",PhongDon= @phongdon"+
                        ",GiaTien= @giatien" +
                        "NgayCapNhat = GETDATE(), TrangThai = @trangthai" +
                        " WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@iddattour", SqlDbType.VarChar).Value = model.IdDatTour;
            sqlCommand.Parameters.Add("@hoten", SqlDbType.NVarChar).Value = model.HoTen;
            sqlCommand.Parameters.Add("@ngaysinh", SqlDbType.DateTime).Value = model.NgaySinh;
            sqlCommand.Parameters.Add("@diachi", SqlDbType.NVarChar).Value = model.DiaChi;
            sqlCommand.Parameters.Add("@gioitinh", SqlDbType.Bit).Value = model.GioiTinh;
            sqlCommand.Parameters.Add("@idloaikhach", SqlDbType.Int).Value = model.IdLoaiKhach;
            sqlCommand.Parameters.Add("@iddotuoi", SqlDbType.Int).Value = model.IdDoTuoi;
            sqlCommand.Parameters.Add("@phongdon", SqlDbType.VarChar).Value = model.PhongDon;
            sqlCommand.Parameters.Add("@giatien", SqlDbType.Decimal).Value = model.GiaTien;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Int).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC12 model)
        {
            var query = "DELETE FROM [VYC-12] " +
                        " WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Int).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Get by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ModelVYC12> GetById(int id)
        {
            var list = new List<ModelVYC12>();
            var query = "SELECT [Id],[IdDatTour],[HoTen],[NgaySinh],[DiaChi],[GioiTinh],[IdLoaiKhach],[IdDoTuoi],[PhongDon],[GiaTien],[NgayCapNhat],[TrangThai] FROM [VYC-12] WHERE Id = " + id;
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC12();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.IdDatTour = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString();
                model.HoTen = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString();
                model.NgaySinh = (dataTable.Rows[i][2] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][2];
                model.DiaChi = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString();
                model.GioiTinh = (dataTable.Rows[i][3] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][3]);
                model.IdLoaiKhach = (dataTable.Rows[i][3] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][3]);
                model.IdDoTuoi = (dataTable.Rows[i][3] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][3]);
                model.PhongDon = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString();
                model.GiaTien = (dataTable.Rows[i][3] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][3]);
                model.NgayCapNhat = (dataTable.Rows[i][2] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][2];
                model.TrangThai = (dataTable.Rows[i][3] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][3]);

                list.Add(model);
            }
            return list;
        }
    }
}